﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Public Class DAORemito

    '/////////////////////////BUSCAR//////////////////////////
    Public Function DAOBuscarRemitos(ByVal idCli As Integer) As NEGCliente
        'metodo para buscar todos los datos del cliente
        Dim cli As New NEGCliente()
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Dim sql As String = "SELECT c.ID_CLIENTE,c.RAZON_SOCIAL, c.MAIL, c.TELEFONO, c.OBSERVACIONES,d.CALLE, d.NRO_CALLE, d.PISO, d.DEPTO,"
        sql += " d.COD_POSTAL AS COD_POSTAL,b.ID_BARRIO, l.ID_LOCALIDAD FROM dbo.T_CLIENTES c LEFT OUTER JOIN T_DOMICILIOS d "
        sql += " ON c.ID_DOMICILIO = D.ID_DOMICILIO LEFT OUTER JOIN T_BARRIOS b ON d.ID_BARRIO = b.ID_BARRIO "
        sql += " LEFT OUTER JOIN T_LOCALIDADES l ON B.ID_LOCALIDAD = L.ID_LOCALIDAD WHERE c.ID_CLIENTE = " & idCli

        Dim cmd As New SqlCommand()

        cn.Open()

        cmd.CommandText = sql
        cmd.Connection = cn
        cmd.CommandType = CommandType.Text

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        While dr.Read()
            cli.nroCli = Integer.Parse((dr("ID_CLIENTE")).ToString())
            cli.razonSocial = (dr("RAZON_SOCIAL")).ToString()
            cli.mail = (dr("MAIL")).ToString()
            cli.tel = Integer.Parse((dr("TELEFONO")).ToString())
            cli.observ = (dr("OBSERVACIONES")).ToString()

            cli.calle = (dr("CALLE")).ToString()

            cli.nro = Integer.Parse((dr("NRO_CALLE")).ToString())
            cli.piso = Integer.Parse((dr("PISO")).ToString())
            cli.depto = Integer.Parse((dr("DEPTO")).ToString())
            cli.cp = Integer.Parse((dr("COD_POSTAL")).ToString())
            cli.barrio = Integer.Parse((dr("ID_BARRIO")).ToString())

            cli.localidad = Integer.Parse((dr("ID_LOCALIDAD")).ToString())
        End While
        cn.Close()
        Return cli

    End Function


    '//////////////////////////GUARDAR/////////////////////////

    Public Function DAOInsertCliente(ByVal cli As NEGCliente) As Boolean
        ' inserto el cliente con su domicilio
        Dim band As Boolean = False
        'bandera para ver si la transaccion termina bien o no
        Dim tabla As New Data.DataTable
        Dim con As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString
        Dim cn As New SqlConnection(con)

        Try
            cn.Open()
            Dim sql As New SqlCommand("clientes_INSERTAR", cn)
            'primero el nombre del sp y despues la conexion
            sql.CommandType = CommandType.StoredProcedure

            sql.Parameters.Add("@CALLE", SqlDbType.NVarChar, 50)
            sql.Parameters("@CALLE").Value = cli.calle
            sql.Parameters.Add("@NRO_CALLE", SqlDbType.Int, 4)
            sql.Parameters("@NRO_CALLE").Value = cli.nro


            sql.Parameters.Add("@PISO", SqlDbType.Int, 4)
            sql.Parameters("@PISO").Value = cli.piso

            sql.Parameters.Add("@DEPTO", SqlDbType.Int, 4)
            sql.Parameters("@DEPTO").Value = cli.piso

            sql.Parameters.Add("@ID_BARRIO", SqlDbType.Int, 4)
            sql.Parameters("@ID_BARRIO").Value = cli.barrio

            sql.Parameters.Add("@COD_POSTAL", SqlDbType.Int, 4)
            sql.Parameters("@COD_POSTAL").Value = cli.cp

            sql.Parameters.Add("@RAZON_SOCIAL", SqlDbType.NVarChar, 50)
            sql.Parameters("@RAZON_SOCIAL").Value = cli.razonSocial

            sql.Parameters.Add("@TELEFONO", SqlDbType.NChar, 15)
            sql.Parameters("@TELEFONO").Value = cli.tel

            sql.Parameters.Add("@MAIL", SqlDbType.NVarChar, 50)
            sql.Parameters("@MAIL").Value = cli.mail


            sql.Parameters.Add("@OBSERVACIONES", SqlDbType.NVarChar, 50)
            sql.Parameters("@OBSERVACIONES").Value = cli.observ

            sql.Parameters.Add("@NRO_ING_BRUTOS", SqlDbType.Int)
            sql.Parameters("@NRO_ING_BRUTOS").Value = cli.ingBrutos

            sql.Parameters.Add("@ID_COND_IVA", SqlDbType.Int)
            sql.Parameters("@ID_COND_IVA").Value = cli.idCondIva

            sql.Parameters.Add("@CUIT", SqlDbType.NChar, 13)
            sql.Parameters("@CUIT").Value = cli.cuit

            If cli.ctaCte = 0 Then
                sql.Parameters.Add("@IDCUENTA", SqlDbType.Int)
                sql.Parameters("@IDCUENTA").Value = DBNull.Value
            Else
                sql.Parameters.Add("@IDCUENTA", SqlDbType.Int)
                sql.Parameters("@IDCUENTA").Value = cli.ctaCte
            End If

            sql.Parameters.Add("@LIMITE_CRE", SqlDbType.Int)
            sql.Parameters("@LIMITE_CRE").Value = cli.limiteCred

            sql.ExecuteNonQuery()


            band = True
        Catch e As Exception
            band = False
        Finally
            cn.Close()
        End Try
        Return band
    End Function
End Class
